import pandas as pd
import numpy as np
# 读取数据
df1 = pd.read_excel('./datas/水果卖场产品销量数据.xls')
df2 = pd.read_excel('./datas/水果卖场会员数据.xls')
df3 = pd.read_excel('./datas/水果卖场销售数据.xls')
#异常数据处理
df1 = df1.dropna()
df2 = df2.dropna()
df3 = df3.dropna()
# 将两表数据合并
market_name = df1['卖场名称'].unique().tolist()

# 获取全部卖场名称
def get_market_name():
    return market_name


# ---------------------------------左上 气泡图 -----------------------------------------------------------------

def h_task1(markerName):
    df_name = df1[df1['卖场名称'] == markerName]
    df_k = df_name['产品名称'].values.tolist()
    df_v = df_name['销量'].values.tolist()
    d_saleSum = df_name.groupby('产品名称')['销量'].sum()
    d_proName = d_saleSum.index.tolist()
    df_datas = list(zip(df_k, df_v))
    return {
        'data': df_datas,
        'index': d_proName,
        'value': d_saleSum.values.tolist()
    }


def h_task1b(markerName):
    data_items = []
    df_name = df1[df1['卖场名称'] == markerName]
    d_saleSum = df_name.groupby('产品名称')['销量'].sum().to_dict()
    for k, v in d_saleSum.items():
        data_items.append({'name': k, 'value': v})
    return {
        'wordCloud': data_items
    }


# task2实现
def h_task2(markerName):
    df_name = df1[df1['卖场名称'] == markerName]
    x_key, x_value = 0, 0
    dataLink_items, data_items = [], []
    d_TypeAll = df_name.groupby('产品名称')['产品分类'].agg("first").to_dict()
    for is_key, item in [(True, key) for key in d_TypeAll.keys()] + [(False, value) for value in d_TypeAll.values()]:
        if is_key:
            data_items.append({'name': item, 'x': x_key, 'y': 40})
            x_key += 30
        else:
            data_items.append({'name': item, 'x': x_value, 'y': 110})
            x_value += 30
    for k, v in d_TypeAll.items():
        dataLink_items.append({'source': k, 'target': v})
    dataLink_items.append({'source': '水蜜桃', 'target': '芭蕉科'})
    return {
        'data': data_items,
        'links': dataLink_items
    }


# task3实现
def h_task3(markerName):
    d_nameData = df3['卖场名称'].drop_duplicates().values.tolist()
    d_saleBudget = df3.groupby('卖场名称')['销售预算'].apply(list).values.tolist()
    return {
        'index': d_nameData,
        'value': d_saleBudget
    }


# task3b实现
def h_task3b(markerName):
    datas = []
    d_nameData = df3[df3['卖场名称'] == markerName]
    d_saleBudget = d_nameData['销售预算'].values.tolist()
    datas.append(d_saleBudget)
    return {
        'value': datas
    }


# handle task4
def h_task4(markerName):
    d_nameDatas = df3[df3['卖场名称'] == markerName]
    d_daySaleData = df1[df1['卖场名称'] == markerName]
    d_daySaleData = d_daySaleData['销量'].sum() / 30
    d_ac = int(d_nameDatas['AC'].sum())
    d_tc = int(d_nameDatas['TC'].sum())
    d_sales = int(d_nameDatas['销售'].sum())
    return {
        'AC': d_ac,
        'TC': d_tc,
        '日商': d_daySaleData,
        '销售': d_sales
    }


# handle task5
def h_task5(markerName):
    total_datas = []
    total_avgDatas = []
    nullData = ['上海', '海南', '南海诸岛']
    d_nameDatas = df1[df1['卖场名称'] == markerName]
    max_sale = float(d_nameDatas['销量'].max())
    max_saleNum = d_nameDatas.groupby('省份名称').sum().max().count()
    max_saleAvg = d_nameDatas.groupby('省份名称')['销量'].sum().max()
    max_saleAvg = float((max_saleAvg / max_saleNum).round(2))
    # 计算销售和门店数总和
    total_sales = df3[df3['卖场名称'] == markerName].groupby('省份')['销售'].sum()
    total_stores = df3[df3['卖场名称'] == markerName].groupby('省份')['门店数'].sum()
    # 计算平均销售并保留两位小数
    average_sales = (total_sales / total_stores).round(2).to_dict()
    for k, v in average_sales.items():
        total_datas.append({'name': k, 'value': v})
    for i in range(len(nullData)):
        total_datas.append({'name': nullData[i], 'value': 0})
    total_storesAvgSales = ((d_nameDatas.groupby('省份名称')['销量'].sum()).to_list() / d_nameDatas.groupby('省份名称')['销量'].count()).to_dict()
    for k, v in total_storesAvgSales.items():
        total_avgDatas.append({'name': k, 'value': v})
    for i in range(len(nullData)):
        total_avgDatas.append({'name': nullData[i], 'value': 0})
    return {
        'max_销量': max_sale,
        'max_门店平均销量': max_saleAvg,
        '销量': total_datas,
        '门店平均销量': total_avgDatas
    }


# handle task6
def h_task6(markerName):
    d1_nameDatas = df1[df1['卖场名称'] == markerName]
    type_items = []
    d_types = d1_nameDatas.groupby('产品分类')['产品分类'].agg('first').values.tolist()
    d_sales = d1_nameDatas['销量'].values.tolist()
    # 创建 type_items，包含产品分类的名称和其对应的索引值
    type_items = [{'name': name, 'value': index + 1} for index, name in enumerate(d_types)]
    items_data = d1_nameDatas['产品分类'].values.tolist()
    type_dict = {item['name']: item['value'] for item in type_items}
    # 合并 items_data 和对应的 value 值
    merged_data = [[item, type_dict[item]] for item in items_data]
    return {
        'data': merged_data,
        '产品分类': d_types,
        '销量': d_sales
    }


def h_task7(markerName):
    h_name = markerName
    totalData = int(df1['销量'].sum())
    nowData = int(df1[df1['卖场名称'] == markerName]['销量'].sum())
    return {
        'name': h_name,
        'total': totalData,
        'value': nowData
    }


def h_task8(markerName):
    vip_datas = df2[df2['卖场名称'] == markerName]
    vMonth = vip_datas.groupby('month')['month'].agg('first').values.tolist()
    vip_addNumber = vip_datas.groupby('month')['会员增加数'].sum().tolist()
    return {
        'index': vMonth,
        'value': vip_addNumber
    }
